Intermediate PHP and MySQL

Introduction

Download the code for this lesson.

Congratulations, you've made it to Lesson 8! You've covered a lot of ground so far and have a pretty fancy Web application to show for it. There are only a couple more features to cover in the storefront application.

In the previous lesson, you saw how to create the shopping cart. Now it's time to put that knowledge to use and allow your customers to start shopping!

First, we'll look at how to display your products in an online catalog. Then we'll discuss letting your customers select items directly from the catalog and placing the products in their shopping cart.

Next, you'll learn how to track products in the Web store. There are several different techniques for tracking product inventory and knowing what shopper has what products reserved. You'll discover what's involved in making sure your customers can purchase the products they see in your store.

Finally, you'll work on being able to update existing items in the cart. Shoppers are bound to change their minds, and your Web site will have to accommodate them as they want to remove items from the shopping cart or change the quantity of an item in the cart before placing their final order.

Let's move on to Chapter 2 and take a look at what you need to do to let your customers start shopping.

Online Shopping With Carts

The most basic function in your Web store is to allow customers to browse the online catalog of products, select items, and place them in their shopping cart. While this seems like a simple process, there's a lot going on in the background that you need to worry about. First, let's look at how to present the store catalog.

Displaying the Catalog

So far in our project, you've seen that the navigation section on the main Web page lists the different categories used in the online store. This helps break down the catalog into manageable sections for both you and your customers.

Each category link in the navigation page directs the customer to a Web page that displays the products within that category using the buyproducts content page:

The beauty of using functions is being able to use the same code in multiple places. The buyproducts.inc.php file utilizes the same showproducts() function you used in the administration back-end application to create a table of the products for the specific category. Let's create the buyproducts.inc.php program:

Create the file buyproducts.inc.php in the store folder.
1. Open the file in a text editor, and add the following code:

Print code


<?php
   $catid = $_GET['cat'];
   $query="SELECT name from categories WHERE catid = $catid";
   $result = mysql_query($query);
   $row=mysql_fetch_array($result, MYSQL_ASSOC);
   echo "<h2>{$row['name']} - Click on a product to purchase it</h2>\n";
   if (!isset($_GET['page']))
      $page = 1;
   else
      $page = $_GET['page'];
showproducts($catid, $page, "index.php?content=buyproducts", "index.php?content=updatecart");
?>

2. Save the file and exit the editor.

There's not too much to this code. The link in the navigation section passes the category ID value to the buyproducts.inc.php code. The code takes that value and finds the actual name of the category in the categories table and then displays the category name on the top of the main section. Next, it checks the HTML variable page to see if the showproducts() function passed on the next page value. If not, it assigns the variable to the value of 1 to display the first page.

Finally, it calls the same showproducts() function we used in our administration back-end application to display the products on the main Web page.

A sample catalog Web page

The showproducts() function specifies the two URLs required for the product list. The first URL points to the original Web page for displaying more pages of data. The second URL points to the Web page used when a customer selects an individual product. This code uses the URL:

When you click the link for a product, it automatically adds the product ID to the end of this link and sends the customer to the index.php page using the updatecart.inc.php file as the content for the main section.

Adding Items to the Cart

The updatecart.inc.php file produces a simple HTML form for the customer to select the quantity of the product to purchase.

The Update Cart Web page

Let's create the code to build this Web page:

1. Create a file called updatecart.inc.php in the store folder.
2. Open the file in an editor, and enter the following code:

Print code


<?php
   $prodid = $_GET['id'];
   echo "<h2>Add item to cart</h2>\n";
   $query = "SELECT description, price from products where prodid = $prodid";
   $result = mysql_query($query);
   $row=mysql_fetch_array($result, MYSQL_ASSOC);
   $description = $row['description'];
   $price = $row['price'];
   $quantity = 1;
   echo "<form action=\"index.php\" method=\"post\">\n";
   echo "<table width=\"100%\" cellpadding=\"1\" border=\"1\">\n";
   echo "<tr><td>Image</td><td>Product</td><td>Price</td><td>Quantity</td></tr>\n";
   echo "<tr><td><img src=\"showimage.php?id=$prodid\" width=\"80\" height=\"60\"></td>\n";
   echo "<td>$description</td><td>$price</td>\n";
   echo "<td><input type=\"text\" name=\"quantity\" value=\"$quantity\" size=\"3\"</td></tr>\n";
   echo "</table>\n";
   echo "<input type=\"hidden\" name=\"content\" value=\"addtocart\">\n";
   echo "<input type=\"hidden\" name=\"prodid\" value=\"$prodid\">\n";
   echo "<input type=\"submit\" value=\"Add to cart\">\n";
   echo "</form>\n";
?>

3. Save the file and exit the editor.

The code displays the standard product information (the product image, name, and price), along with a form to enter the quantity to purchase. The form uses a default value of 1 to help make it easier for your shopper to select just a single item.

When your customer clicks the Add to cart button, the HTML form passes the product ID and quantity values to the index.php page using the POST method. It assigns the content HTML variable the value of addtocart.

This causes the index.php file to include the addtocart.inc.php file in the main section. This code attempts to add the product to the current list of products in the shopping cart session array variable.

Now let's build the code for that file:

1. Create a file called addtocart.inc.php in the store folder.
2. Open the file in an editor, and enter the following code:


Print code


<?php
   $prodid = $_POST['prodid'];
   $quantity = $_POST['quantity'];
   $query = "SELECT quantity, description FROM products WHERE prodid = $prodid";
   $result = mysql_query($query);
   $row = mysql_fetch_array($result);
   $stock = $row[0];
   $description = $row[1];
   if ($quantity > $stock)
   {
      echo "<h2>Sorry, there are only $stock $description left in stock</h2>\n";
      echo "<h2>Please select another quantity</h2>\n";
   } else
   {
      if (isset($_SESSION['cart'][$prodid]))
      {
         $_SESSION['cart'][$prodid] += $quanity;
      } else
      {
         $_SESSION['cart'][$prodid] = $quanity;
      }
      echo "<h2>Product added to cart.</h2>\n";
   }
   echo "<a href=\"index.php\">Continue shopping</a><br>\n";
   echo "<a href=\"index.php?content=checkout\">Check out</a>\n";
?>

3. Save the file and exit the editor.

The addtocart code retrieves the product ID and quantity values from the HTML form data, and then it performs an SQL query to check the current quantity value listed for the product in the products table. If your customer attempts to purchase more than the quantity in stock, the code displays an error message and asks the customer to select another quantity.

If there's enough of the product in stock, the addcart code adds the quantity value to a session cookie array variable using the product ID as the variable:

If you're new to programming, this is a shorthand way of writing the statement:

The += symbol adds the specified value to the assignment variable.

This solution also solves the problem for when a customer decides to purchase more of the same item. If the product ID specified in the array variable already exists, it adds the quantity to the existing array variable value.

That should take care of placing new items into the shopping cart. Come on over to the next chapter and see how we can track the items in the cart.

Tracking Products

Okay, so now you have a customer browsing through the online store with products in his or her shopping cart. However, you'll need to address a small challenge.

Have you ever been in a grocery store and seen a cart full of groceries sitting off to the side with no one attending it? The same thing can happen in our online store. A customer can fill up a shopping cart with products and then walk away from the PC for a while.

This can cause problems with your inventory control, depending on how you handle ordering products. Trying to track product inventory is a notoriously difficult thing for online retailers. Let's take some time out from coding to look at how to deal with this.

Handling Session Cookies

When you produced the addtocart.inc.php code, you checked the quantity in stock of a product before allowing a customer to put the items in the shopping cart. The scenario has one small problem that will need your attention.

The Food Store application attempts to keep track of the entire inventory in the store. The products table tracks the quantity in stock value for each product. Before allowing a customer to purchase a product, you check to make sure there are enough in stock to satisfy the purchase. In the addtocart.inc.php example, you'll notice that we don't do anything to the quantity value in the products table once we allow the customer to place the item in the shopping cart. There's a reason for this.

As customers browse around in the online store, they may place products in the shopping cart and then remove them. If you modified the products table every time a customer places or removes a product, you'd have tons of database updates.

To reduce this problem, the Food Store application uses a slightly different method of tracking inventory:

• It checks to see if an item is in inventory before allowing a customer to place it in the shopping cart, but it doesn't update the database.
• It waits until the customer checks out before reducing the products table inventory by what's in the shopping cart.

This process helps reduce the number of transactions in the database, as you only need to update the product quantity when the customer finally checks out from the store. By reducing the number of transactions, you greatly increase the performance of the application. However, it also creates a small window for trouble.

During the time between when a customer places an item in the shopping cart and checks out, the inventory of the product in the products table is inaccurate. The store application doesn't account for products sitting in a customer's shopping cart. Those items are in limbo, as the official store inventory is concerned.

To get a better understanding of what can happen, let's walk through an example:

1. The products table shows there are three apples in inventory for the store.
2. Customer A places three apples in her cart. The addtocart.inc.php program checks to see that there are three apples in stock according to the products table. So far, so good.
3. Customer B browses through the store and places three apples in his cart. Since customer A hasn't checked out yet, the products table still thinks there are three apples in stock. So customer B is also allowed to place three apples in his cart.
4. Customer B checks out of the store and purchases the items in his shopping cart. At this time, the program reduces the products table quantity for apples by three, leaving no more apples in stock.
5. Customer A finally decides to check out of the store and purchases the items in her shopping cart. Now the products table quantity for apples is zero, and the program realizes that there are no more apples to purchase.

This can be somewhat confusing for the customer and embarrassing for the store manager. There are a couple of ways around this issue:

• Don't track specific quantities when customers order items.
• Minimize the amount of time between ordering and purchasing.

The first method has become a common technique for most online stores. Commercial Web stores warn you if supplies are getting low (mostly as a marketing technique) but will allow you to order products even if they're not in stock. All that changes is an estimated shipping date when the products become available.

If you want to track inventory quantities, you can't take an order for an item that's out of stock. In this case, you should try to minimize the amount of time a customer can spend between placing an item in the shopping cart and checking out. The last thing you want is for a customer to place a product in the shopping cart, walk away from the PC, and come back an hour later trying to purchase it.

To prevent this problem, you need a way for the shopping cart to expire at a preset interval. This way, if a customer leaves the PC with items in the shopping cart, when he or she comes back, the cart will have reset, and the customer will need to select the items again.

You can accomplish this with a special feature of session cookies. I mentioned that a session cookie is active for as long as the browser window session remains active. This isn't 100% true.

Altering the Session Cookie Lifetime

This is the default behavior of session cookies, but you can modify it. Some sites (especially ones that process sensitive data) use the timeout feature for the session cookie. If the customer hasn't finished the Web transaction within a preset amount of time, the session cookie expires, even if the browser window is still open.

To force session cookies to expire prematurely, you must make a setting change in the PHP configuration file. The setting is called session.cookie_lifetime.

This entry controls how many seconds the session cookie remains active while the Web browser is active. The default value of 0 indicates that the session remains active for the duration of the time the Web browser is active. If you set this value to anything other than zero, the session cookie expires when the time limit is expired, even if the Web browser is still active.

This value is set in the php.ini configuration file. You can modify this value in WampServer by following these steps:

1. Start the WampServer services on the PC.
2. Click the WampServer icon in the system tray, and select PHP.
3. Select the php.ini configuration file.
4. In the Notepad editor, search for the term lifetime. This will produce the line:

   session.cookie_lifetime = 0

5. Change the zero value to 300 seconds.
6. Save the php.ini configuration file and exit the editor.

Now your customers must check out within five minutes of starting their session with the Web store. After five minutes, their shopping cart will disappear without warning, and a new one will start.

Now let's continue on to Chapter 4 and discuss modifying the contents of the shopping cart.

Updating an Order

Often a customer will place an item in the cart, only to decide to remove it later on. Or instead, the customer may decide to go back and request more (or less) of an individual item. Instead of making the customer replace the entire shopping cart contents, you should have a system that lets the customer modify individual products within the cart.

The Food Store application includes code to allow the customer to review and modify an existing shopping cart. The navigation bar you created earlier contains a link to review the contents of the shopping cart:

This link uses the reviewcart.inc.php file to display the shopping cart contents in the main section of the Web page.

The Review Cart Web page

Let's create this file now.

1. Create the file reviewcart.inc.php in the store folder.
2. Open the file with a text editor, and enter the following code:

Print code


<?php
   echo "<h2>Review your shopping cart contents</h2><br>\n";
   $total = 0;
   echo "<table width=\"100%\" cellpadding=\"1\" border=\"1\">\n";
   echo "<tr><td>Product</td><td>Price</td><td>Quantity</td><td>Total</td><td> </td>\n";
   foreach($_SESSION['cart'] as $prodid => $quantity)
   {
      $query = "SELECT description, price FROM products WHERE prodid = $prodid";
      $result = mysql_query($query);
      $row = mysql_fetch_array($result, MYSQL_ASSOC);
      $description = $row['description'];
      $price = $row['price'];
      $subtotal = $price * $quantity;
      $total += $subtotal;
      printf("<tr><td>%s</td><td>%s</td><td>%d</td><td>$%.2f</td>\n",
              $description, $price, $quantity, $subtotal);
      echo "<td><a href=\"index.php?content=updateitem&id=$prodid\">Modify</a></td></tr>\n";
   }
   printf("<tr><td colspan=\"3\"><b>Total</b></td><td>$%.2f</td></tr>\n", $total);
   echo "</table>\n";
?>

3. Save the file and exit the text editor.

This code should look familiar to you. It uses the same code as the real-time cart display section on the main Web page, which displays the contents of the shopping cart.

Since the items are indexed using their prodid values, the array values are numerical, but not necessarily in order. This would make iterating through the individual products in the cart impossible using a standard for loop. Instead, we can use our new friend, the foreach loop:

The foreach loop retrieves the individual prodid keys and the associated quantities stored as values for each key. After retrieving the product ID and quantity values from the session cookie, the code sends an SQL query to the MySQL database to retrieve the description and price of the product. This allows you to display the product in a form that the customer can understand.

As the code displays each product in the shopping cart, it also produces a link for the customer:

This link takes the customer to the updateitem.inc.php include file. This allows the customer to update or remove the product in the shopping cart.

The Update item Web page

Let's make this file next:

1. Create a file called updateitem.inc.php in the store folder.
2. Open the file in a text editor and add the following code:

Print code


<?php
   echo "<h2>Update item in cart</h2><br>\n";
   $prodid = $_GET['id'];
   $query = "SELECT description, price FROM products where prodid = $prodid";
   $result = mysql_query($query);
   $row = mysql_fetch_array($result, MYSQL_ASSOC);
   $description = $row['description'];
   $price = $row['price'];
   $quantity = $_SESSION['cart'][$prodid];
   echo "<form action=\"index.php\" method=\"post\">\n";
   echo "<input type=\"hidden\" name=\"content\" value=\"changeitem\">\n";
   echo "<input type=\"hidden\" name=\"prodid\" value=\"$prodid\">\n";
   echo "<table width=\"100%\" cellpadding=\"1\" border=\"1\">\n";
   echo "<tr><td><h3>Product ID</h3></td><td>$prodid</td></tr>\n";
   echo "<tr><td><h3>Description</h3></td><td>$description</td></tr>\n";
   printf("<tr><td><h3>Price</h3></td><td>$%.2lf</td></tr>\n", $price);
   echo "<tr><td><h3>Quantity</h3></td><td><input type=\"text\" name=\"quantity\" value=\"$quantity\"></td></tr>\n";
   $total = $price * $quantity;
   printf("<tr><td><h3>Total</h3></td><td>\$%.2lf</td></tr>\n", $total);
   echo "</table>\n";
   echo "<input type=\"submit\" name=\"button\" value=\"Update\">\n";
   echo "<input type=\"submit\" name=\"button\" value=\"Remove Item from cart\">\n";
   echo "</form>\n";
?>

3. Save the file and exit the editor.

This PHP code produces a simple HTML form, and populates the form with the information about the product and the current quantity in the shopping cart. You'll notice that there are two submit buttons in this form. One is to update the quantity value and one to remove the item from the cart.

The final piece of code is the changeitem.inc.php file:

1. Create the file changeitem.inc.php in the store folder.
2. Open the file in a text editor and add the following code:

<?php
   $button = $_POST['button'];
   $prodid = $_POST['prodid'];
   if ($button == "Update")
   {
      $quantity = $_POST['quantity'];
      $_SESSION['cart'][$prodid] = $quantity;
      echo "<h2>Item quantity updated in cart</h2>\n";
      echo "<a href=\"index.php?content=reviewcart\">Review cart</a>\n";
   } else
   {
      unset($_SESSION['cart'][$prodid]);
      echo "<h2>Item removed from cart</h2>\n";
      echo "<a href=\"index.php?content=reviewcart\">Review cart</a>\n";
   }
?>

3. Save the file and exit the text editor.

This simply determines which submit button on the updateitem page the customer clicked. Since both buttons use the same HTML variable, all you need to do is check the value of the HTML variable. If the customer clicks the Update button, the quantity value replaces the appropriate session cookie with the new value. If the customer clicks the Remove button, the code deletes the session cookie using the unset() function.

That's quite a lot of code to cover in one lesson! Let's move over to Chapter 5 and review what we did today.

Summary

Today you learned to manage the customer's shopping cart. Once the cart is created in the session cookie, you can use the standard $_SESSION [] array variable to add products as array keys and the quantity purchased as the array value. This method allows the customer to purchase many items, and they'll all be stored in the session cookie.

We discussed some of the pitfalls of trying to track product inventory in real time while allowing customers to shop online. As a customer decides to purchase an item, you have the choice whether or not to decide if the item is in stock, and prevent the customer from purchasing the product if it isn't. There are multiple ways to handle this, including forcing shopping carts to expire if customers don't check out in a timely fashion.

Finally, you worked through the code to help manage the contents of the shopping cart. If the customer decides to remove or change the quantity of an item, your store application is capable of handling this.

There's just one more piece left to the storefront application. After your customers are finished shopping, you'll want them to check out. In the next lesson, we'll walk through some of the features found in fancy Web store checkout lanes.